package com.sxfq.loansupermarket.backsystem.modular.tcCenter.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.DateTimeKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.CommUtils;
import com.sxfq.loansupermarket.backsystem.core.util.DateUtil;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.ReturnVisitInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.SearchCustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.SearchReturnVisitInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.PayServiceInfo;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.CaseInfo;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.ToothSite;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.TongJiInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.RechargeInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.RefundInfo;
import com.sxfq.loansupermarket.backsystem.modular.system.transfer.UserDto;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.*;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.service.IConsultantService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * 预约客户信息
 *
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/consultantTwo")
public class ConsultantTwoController extends BaseController {

    @Autowired
    IConsultantService service;

    @Autowired
    ICustomerService customerService;

    @Autowired
    private SqlMapper sqlMapper;

    @Autowired
    IReservationsService reservationsService;

    private String PREFIX = "/tcCenter/consultant/";


    /**
     * TC姓名跳转页面 (获取开单操作  和 开单记录的 数据)   传一个客户ID和 预约ID
     */
    @RequestMapping(value = "/consultantToDetail/{id}")
    public String consultantToDetail(@PathVariable String id, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql2 = "select " +
                "  (CASE a.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,a.* from " + this.getTableName() + " a where id= '" + id + "'";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql2, ConsultantInfo.class);
        String sqlresrvations = "select * from reservations_info where id='" + consultantInfo.getReservationsId() + "' limit 0,1";
        //回访详情
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sqlresrvations, ReservationsInfo.class);
        if (reservationsInfo == null) {
            String sqlcu = "select * from " + this.getTableName() + "  where id='" + id + "'";
            CustomerInfo customer = sqlMapper.selectOne(sqlcu, CustomerInfo.class);
            reservationsService.insert(customer);
//            String sqlresrvations1 = "select * from reservations_info where id='" + consultantInfo.getReservationsId() + "' limit 0,1";
//            reservationsInfo = sqlMapper.selectOne(sqlresrvations1, ReservationsInfo.class);
            model.addAttribute("huifang", "");
        } else {
            model.addAttribute("huifang", reservationsInfo.getHuifang());
        }
        String sql1 = "delete from open_bill_info where tc_name='" + ShiroKit.getUser().getAccount() + "' and reservations_id='" + consultantInfo.getReservationsId() + "'";
        sqlMapper.delete(sql1);
        List<PayServiceInfo> payServiceInfoList = service.getPayServiceInfoList();
        //获得客户信息

        //项目
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        ShiroKit.setSessionAttr("customerId", id);
        CustomerInfo customerInfo = customerService.selectOneById(id);

        ShiroKit.setSessionAttr("reservationsId", customerInfo.getReservationsId());
//        String sql1 = "delete from open_bill_info where tc_name='" + ShiroKit.getUser().getAccount() + "' and customer_id='" + ShiroKit.getSessionAttr("customerId") + "'";
//        sqlMapper.delete(sql1);
        //看诊详情
        List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList(consultantInfo.getReservationsId());
        String tcName = "";
        String firstVisitDate = "";
        if (customerInfo != null) {
            tcName = customerInfo.getFieldTc();
            firstVisitDate = customerInfo.getFirstVisitDate();
        }
        for (ConsultantInfo info1 : consultantInfoList) {
            if (info1.getDiagnosisTime() != "" && info1.getDiagnosisTime() != null) {
                info1.setDiagnosisTimeOne(info1.getDiagnosisTime().substring(0, 10));
                info1.setDiagnosisTimeTwo(info1.getDiagnosisTime().substring(11, info1.getDiagnosisTime().length()));
            }
        }
        //就诊记录
        String sql11 = "select left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,t.remarks as remarks," +
                "t.archiving as archiving,t.arrival_store as arrivalStore,t.appointment_pay_service as appointmentPayService ," +
                "t.customer_status as customerStatus,t.status as status from " + this.getTableName() + " t " +
                "where t.reservations_id='" + customerInfo.getReservationsId() + "' order by start_time desc";
        List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql11, CustomerInfo.class);
        for (CustomerInfo infos : customerInfoList) {
            if (infos.getStartTime() != "" && infos.getStartTime() != null) {
                infos.setStartTimeOne(infos.getStartTime().substring(0, 10));
                infos.setStartTimeTwo(infos.getStartTime().substring(11, infos.getStartTime().length()));
            }
        }
        //通过预约ID  获得开单记录
        String tableName1 = tableHead + "deal_info";
        String sql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus,t1.nurse  as nurse, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,t1.get_time as getTime from " + tableName1 + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.reservations_id='" + consultantInfo.getReservationsId() + "' and pay_service_id is not null and t1.deal_status!='未成交' and t1.deal_status!='作废' group by  operationTime order by operationTime desc";
        List<PayServiceInfo> payServiceInfoList1 = sqlMapper.selectList(sql, PayServiceInfo.class);
        String getTime = "";
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        Double accountArrears = 0.00;
        for (PayServiceInfo info : payServiceInfoList1) {
            if (info != null) {
                if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getPayServiceNum() != null) {
                        totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                    }
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay += info.getFoldingPay();
                    }
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPay += info.getServiceActualPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPay);
                    info.setAccountArrears(accountArrears);
                }
                if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                    info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                    info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                }
                getTime = info.getGetTime();
            }
        }

        //病例记录
        //1、先查询当前客户的所有病例记录
        String caseInfoSql = "select * from case_info t1  where t1.reservations_id='" + consultantInfo.getReservationsId() + "' order by t1.operation_time desc";
        List<CaseInfo> caseInfoList = sqlMapper.selectList(caseInfoSql, CaseInfo.class);

        for (CaseInfo infos : caseInfoList) {
            if (infos != null) {
                if (infos.getOperationTime() != "" && infos.getOperationTime() != null) {
                    infos.setOperationTime(infos.getOperationTime().substring(0, 10));
                }
                String sql3 = "select pay_service_name as payServiceName from pay_service_info where id ='" + infos.getPayServiceId() + "'";
                CaseInfo caseInfo1 = sqlMapper.selectOne(sql3, CaseInfo.class);
                if (caseInfo1 == null) {
                    continue;
                }
                infos.setPayServiceName(caseInfo1.getPayServiceName());
            }
        }
        //2.循环遍历每个病例记录，给对应病例的牙位属性赋值
        for (CaseInfo caseInfo : caseInfoList) {
            //然后查询牙位集合
            String toothSIteSql = "select * from tooth_site t1 where t1.case_id='" + caseInfo.getId() + "' ";
            List<ToothSite> toothSiteList = sqlMapper.selectList(toothSIteSql, ToothSite.class);
            for (ToothSite toothSite : toothSiteList) {
                //根据对应的牙位类型，给病例实体的对应牙位属性赋值
                if (toothSite.getYaType().equals("1")) {
                    caseInfo.getToothSite1List().add(toothSite);
                }
                if (toothSite.getYaType().equals("2")) {
                    caseInfo.getToothSite2List().add(toothSite);
                }
                if (toothSite.getYaType().equals("3")) {
                    caseInfo.getToothSite3List().add(toothSite);
                }
                if (toothSite.getYaType().equals("4")) {
                    caseInfo.getToothSite4List().add(toothSite);
                }
                if (toothSite.getYaType().equals("5")) {
                    caseInfo.getToothSite5List().add(toothSite);
                }
            }
        }

        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerId", id);
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("payServiceInfoList1", payServiceInfoList1);
        model.addAttribute("reservationsId", consultantInfo.getReservationsId());
        model.addAttribute("getTime", getTime);
        model.addAttribute("tcName", tcName);
        model.addAttribute("reservations", ShiroKit.getUser().getAccount());
        model.addAttribute("firstVisitDate", firstVisitDate);
        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("consultantInfoList", consultantInfoList);
        model.addAttribute("customerInfoList", customerInfoList);
        model.addAttribute("caseInfoList", caseInfoList);
        return PREFIX + "consultantToDetail.html";
    }


    /**
     * TC姓名跳转页面 (获取开单操作  和 开单记录的 数据)   传一个客户ID和 预约ID
     */
    @RequestMapping(value = "/consultantToDetailhf/{id}")
    public String consultantToDetailhf(@PathVariable String id, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql2 = "select " +
                "  (CASE a.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,a.* from " + this.getTableName() + " a where id= '" + id + "'";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql2, ConsultantInfo.class);
        String sqlresrvations = "select * from reservations_info where id='" + consultantInfo.getReservationsId() + "' limit 0,1";
        //回访详情
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sqlresrvations, ReservationsInfo.class);
        if (reservationsInfo == null) {
            String sqlcu = "select * from " + this.getTableName() + "  where id='" + id + "'";
            CustomerInfo customer = sqlMapper.selectOne(sqlcu, CustomerInfo.class);
            reservationsService.insert(customer);
            String sqlresrvations1 = "select * from reservations_info where id='" + consultantInfo.getReservationsId() + "' limit 0,1";
            reservationsInfo = sqlMapper.selectOne(sqlresrvations1, ReservationsInfo.class);
        }
        String sql1 = "delete from open_bill_info where tc_name='" + ShiroKit.getUser().getAccount() + "' and reservations_id='" + consultantInfo.getReservationsId() + "'";
        sqlMapper.delete(sql1);
        List<PayServiceInfo> payServiceInfoList = service.getPayServiceInfoList();
        //获得客户信息

        //项目
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        ShiroKit.setSessionAttr("customerId", id);
        CustomerInfo customerInfo = customerService.selectOneById(id);

        ShiroKit.setSessionAttr("reservationsId", customerInfo.getReservationsId());
//        String sql1 = "delete from open_bill_info where tc_name='" + ShiroKit.getUser().getAccount() + "' and customer_id='" + ShiroKit.getSessionAttr("customerId") + "'";
//        sqlMapper.delete(sql1);
        //看诊详情
        List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList(consultantInfo.getReservationsId());
        String tcName = "";
        String firstVisitDate = "";
        if (customerInfo != null) {
            tcName = customerInfo.getFieldTc();
            firstVisitDate = customerInfo.getFirstVisitDate();
        }
        for (ConsultantInfo info1 : consultantInfoList) {
            if (info1.getDiagnosisTime() != "" && info1.getDiagnosisTime() != null) {
                info1.setDiagnosisTimeOne(info1.getDiagnosisTime().substring(0, 10));
                info1.setDiagnosisTimeTwo(info1.getDiagnosisTime().substring(11, info1.getDiagnosisTime().length()));
            }
        }
        //就诊记录
        String sql11 = "select left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,t.remarks as remarks," +
                "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status from " + this.getTableName() + " t where t.reservations_id='" + customerInfo.getReservationsId() + "' order by start_time desc";
        List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql11, CustomerInfo.class);
        for (CustomerInfo infos : customerInfoList) {
            if (infos.getStartTime() != "" && infos.getStartTime() != null) {
                infos.setStartTimeOne(infos.getStartTime().substring(0, 10));
                infos.setStartTimeTwo(infos.getStartTime().substring(11, infos.getStartTime().length()));
            }
        }
        //通过预约ID  获得开单记录
        String tableName1 = tableHead + "deal_info";
        String sql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus,t1.nurse  as nurse, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,t1.get_time as getTime from " + tableName1 + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.reservations_id='" + consultantInfo.getReservationsId() + "' and pay_service_id is not null and t1.deal_status!='未成交' and t1.deal_status!='作废' group by  operationTime order by operationTime desc";
        List<PayServiceInfo> payServiceInfoList1 = sqlMapper.selectList(sql, PayServiceInfo.class);
        String getTime = "";
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        Double accountArrears = 0.00;
        for (PayServiceInfo info : payServiceInfoList1) {
            if (info != null) {
                if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getPayServiceNum() != null) {
                        totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                    }
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay += info.getFoldingPay();
                    }
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPay += info.getServiceActualPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPay);
                    info.setAccountArrears(accountArrears);
                }
                if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                    info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                    info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                }
                getTime = info.getGetTime();
            }
        }

        //病例记录
        //1、先查询当前客户的所有病例记录
        String caseInfoSql = "select * from case_info t1  where t1.reservations_id='" + consultantInfo.getReservationsId() + "' order by t1.operation_time desc";
        List<CaseInfo> caseInfoList = sqlMapper.selectList(caseInfoSql, CaseInfo.class);

        for (CaseInfo infos : caseInfoList) {
            if (infos != null) {
                if (infos.getOperationTime() != "" && infos.getOperationTime() != null) {
                    infos.setOperationTime(infos.getOperationTime().substring(0, 10));
                }
                String sql3 = "select pay_service_name as payServiceName from pay_service_info where id ='" + infos.getPayServiceId() + "'";
                CaseInfo caseInfo1 = sqlMapper.selectOne(sql3, CaseInfo.class);
                if (caseInfo1 != null) {
                    infos.setPayServiceName(caseInfo1.getPayServiceName());
                } else {
                    infos.setPayServiceName("");
                }
            }
        }
        //2.循环遍历每个病例记录，给对应病例的牙位属性赋值
        for (CaseInfo caseInfo : caseInfoList) {
            if (caseInfo != null) {
                //然后查询牙位集合
                String toothSIteSql = "select * from tooth_site t1 where t1.case_id='" + caseInfo.getId() + "' ";
                List<ToothSite> toothSiteList = sqlMapper.selectList(toothSIteSql, ToothSite.class);
                for (ToothSite toothSite : toothSiteList) {
                    //根据对应的牙位类型，给病例实体的对应牙位属性赋值
                    if (toothSite.getYaType().equals("1")) {
                        caseInfo.getToothSite1List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("2")) {
                        caseInfo.getToothSite2List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("3")) {
                        caseInfo.getToothSite3List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("4")) {
                        caseInfo.getToothSite4List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("5")) {
                        caseInfo.getToothSite5List().add(toothSite);
                    }
                }
            }
        }

        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerId", id);
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("payServiceInfoList1", payServiceInfoList1);
        model.addAttribute("reservationsId", consultantInfo.getReservationsId());
        model.addAttribute("getTime", getTime);
        model.addAttribute("tcName", tcName);
        model.addAttribute("reservations", ShiroKit.getUser().getAccount());
        model.addAttribute("firstVisitDate", firstVisitDate);
        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("reservationsInfo", reservationsInfo);
        model.addAttribute("consultantInfoList", consultantInfoList);
        model.addAttribute("customerInfoList", customerInfoList);
        model.addAttribute("caseInfoList", caseInfoList);
        return PREFIX + "consultantToDetailhf.html";
    }

    /**
     * TC姓名跳转页面 (获取开单操作  和 开单记录的 数据)   传一个客户ID和 预约ID
     */
    @RequestMapping(value = "/consultantToDetailkz/{id}")
    public String consultantToDetailkz(@PathVariable String id, Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql2 = "select " +
                "  (CASE a.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,a.* from " + this.getTableName() + " a where id= '" + id + "'";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql2, ConsultantInfo.class);
        String sqlresrvations = "select * from reservations_info where id='" + consultantInfo.getReservationsId() + "' limit 0,1";
        //回访详情
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sqlresrvations, ReservationsInfo.class);
        if (reservationsInfo == null) {
            String sqlcu = "select * from " + this.getTableName() + "  where id='" + id + "'";
            CustomerInfo customer = sqlMapper.selectOne(sqlcu, CustomerInfo.class);
            reservationsService.insert(customer);
            String sqlresrvations1 = "select * from reservations_info where id='" + consultantInfo.getReservationsId() + "' limit 0,1";
            reservationsInfo = sqlMapper.selectOne(sqlresrvations1, ReservationsInfo.class);
        }
        String sql1 = "delete from open_bill_info where tc_name='" + ShiroKit.getUser().getAccount() + "' and reservations_id='" + consultantInfo.getReservationsId() + "'";
        sqlMapper.delete(sql1);
        List<PayServiceInfo> payServiceInfoList = service.getPayServiceInfoList();
        //获得客户信息

        //项目
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        ShiroKit.setSessionAttr("customerId", id);
        CustomerInfo customerInfo = customerService.selectOneById(id);

        ShiroKit.setSessionAttr("reservationsId", customerInfo.getReservationsId());
//        String sql1 = "delete from open_bill_info where tc_name='" + ShiroKit.getUser().getAccount() + "' and customer_id='" + ShiroKit.getSessionAttr("customerId") + "'";
//        sqlMapper.delete(sql1);
        //看诊详情
        List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList(consultantInfo.getReservationsId());
        String tcName = "";
        String firstVisitDate = "";
        if (customerInfo != null) {
            tcName = customerInfo.getFieldTc();
            firstVisitDate = customerInfo.getFirstVisitDate();
        }
        for (ConsultantInfo info1 : consultantInfoList) {
            if (info1.getDiagnosisTime() != "" && info1.getDiagnosisTime() != null) {
                info1.setDiagnosisTimeOne(info1.getDiagnosisTime().substring(0, 10));
                info1.setDiagnosisTimeTwo(info1.getDiagnosisTime().substring(11, info1.getDiagnosisTime().length()));
            }
        }
        //就诊记录
        String sql11 = "select left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,t.remarks as remarks," +
                "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status from " + this.getTableName() + " t where t.reservations_id='" + customerInfo.getReservationsId() + "' order by start_time desc";
        List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql11, CustomerInfo.class);
        for (CustomerInfo infos : customerInfoList) {
            if (infos.getStartTime() != "" && infos.getStartTime() != null) {
                infos.setStartTimeOne(infos.getStartTime().substring(0, 10));
                infos.setStartTimeTwo(infos.getStartTime().substring(11, infos.getStartTime().length()));
            }
        }
        //通过预约ID  获得开单记录
        String tableName1 = tableHead + "deal_info";
        String sql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus,t1.nurse  as nurse, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,t1.get_time as getTime from " + tableName1 + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.reservations_id='" + consultantInfo.getReservationsId() + "' and pay_service_id is not null and t1.deal_status!='未成交' and t1.deal_status!='作废' group by  operationTime order by operationTime desc";
        List<PayServiceInfo> payServiceInfoList1 = sqlMapper.selectList(sql, PayServiceInfo.class);
        String getTime = "";
        /*Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        Double accountArrears = 0.00;*/
        for (PayServiceInfo info : payServiceInfoList1) {
            if (info != null) {
                /*if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getPayServiceNum() != null) {
                        totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                    }
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay += info.getFoldingPay();
                    }
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPay += info.getServiceActualPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPay);
                    info.setAccountArrears(accountArrears);
                }*/
                if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                    info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                    info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                }
                getTime = info.getGetTime();
            }
        }

        //病例记录
        //1、先查询当前客户的所有病例记录
        String caseInfoSql = "select * from case_info t1  where t1.reservations_id='" + consultantInfo.getReservationsId() + "' order by t1.operation_time desc";
        List<CaseInfo> caseInfoList = sqlMapper.selectList(caseInfoSql, CaseInfo.class);

        for (CaseInfo infos : caseInfoList) {
            if (infos != null) {
                if (infos.getOperationTime() != "" && infos.getOperationTime() != null) {
                    infos.setOperationTime(infos.getOperationTime().substring(0, 10));
                }
                String sql3 = "select pay_service_name as payServiceName from pay_service_info where id ='" + infos.getPayServiceId() + "'";
                CaseInfo caseInfo1 = sqlMapper.selectOne(sql3, CaseInfo.class);
                if (caseInfo1 != null) {
                    infos.setPayServiceName(caseInfo1.getPayServiceName());
                } else {
                    infos.setPayServiceName("");
                }
            }
        }
        //2.循环遍历每个病例记录，给对应病例的牙位属性赋值
        for (CaseInfo caseInfo : caseInfoList) {
            if (caseInfo != null) {
                //然后查询牙位集合
                String toothSIteSql = "select * from tooth_site t1 where t1.case_id='" + caseInfo.getId() + "' ";
                List<ToothSite> toothSiteList = sqlMapper.selectList(toothSIteSql, ToothSite.class);
                for (ToothSite toothSite : toothSiteList) {
                    //根据对应的牙位类型，给病例实体的对应牙位属性赋值
                    if (toothSite.getYaType().equals("1")) {
                        caseInfo.getToothSite1List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("2")) {
                        caseInfo.getToothSite2List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("3")) {
                        caseInfo.getToothSite3List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("4")) {
                        caseInfo.getToothSite4List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("5")) {
                        caseInfo.getToothSite5List().add(toothSite);
                    }
                }
            }
        }

        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerId", id);
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("payServiceInfoList1", payServiceInfoList1);
        model.addAttribute("reservationsId", consultantInfo.getReservationsId());
        model.addAttribute("getTime", getTime);
        model.addAttribute("tcName", tcName);
        model.addAttribute("reservations", ShiroKit.getUser().getAccount());
        model.addAttribute("firstVisitDate", firstVisitDate);
        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("reservationsInfo", reservationsInfo);
        model.addAttribute("consultantInfoList", consultantInfoList);
        model.addAttribute("customerInfoList", customerInfoList);
        model.addAttribute("caseInfoList", caseInfoList);
        return PREFIX + "consultantToDetailkz.html";
    }


    /**
     * 回访姓名跳转页面 () 获取看诊详情  和  回访详情
     */
    @RequestMapping(value = "/consultantNoDetail/{cr}")
    public String consultantNoDetail(@PathVariable String cr, Model model) {
        String id = cr.split(",")[0];
        String reservationsId = cr.split(",")[1];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }

        String selectsql = "select * from " + this.getTableName() + " where reservations_id='" + reservationsId + "' order by arrival_time asc limit 0,1";
        CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
        //看诊详情
        List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList(reservationsId);
        String tcName = "";
        String firstVisitDate = "";
        if (customerInfo != null) {
            tcName = customerInfo.getFieldTc();
            firstVisitDate = customerInfo.getFirstVisitDate();
        }
        for (ConsultantInfo info1 : consultantInfoList) {
            if (info1.getDiagnosisTime() != "" && info1.getDiagnosisTime() != null) {
                info1.setDiagnosisTimeOne(info1.getDiagnosisTime().substring(0, 10));
                info1.setDiagnosisTimeTwo(info1.getDiagnosisTime().substring(11, info1.getDiagnosisTime().length()));
            }
        }
        String sql = "select * from " + this.getTableName() + " t2 where reservations_id='" + reservationsId + "' order by t2.arrival_time desc limit 0,1";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql, ConsultantInfo.class);
        String sql1 = "select * from reservations_info where id='" + reservationsId + "' limit 0,1";
        //回访详情
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql1, ReservationsInfo.class);
        List<AddVisitedInfo> revisitRecordList = service.getRevisitRecordList(reservationsId);
        //成交记录
        String tableName1 = tableHead + "deal_info";
        String sql3 = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime," +
                "sum(t1.service_actual_pay) as totleServiceActualPay,sum(t1.folding_pay) as totleFoldingPay," +
                "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "group_concat(distinct t2.pay_service_name) as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,t1.get_time as getTime ," +
                " group_concat(distinct t1.doctor) as doctor" +
                " from " + tableName1 + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.reservations_id='" + customerInfo.getReservationsId() + "' and pay_service_id is not null and t1.deal_status='已成交'" +
                " group by  operationTime order by operationTime desc";
        List<PayServiceInfo> payServiceInfoList = sqlMapper.selectList(sql3, PayServiceInfo.class);
        String sql2 = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,group_concat(distinct t1.remarks) as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,t1.get_time as getTime ,group_concat(t1.doctor) as doctor\n" +
                " from " + tableName1 + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.reservations_id='" + customerInfo.getReservationsId() + "' and pay_service_id is not null and t1.operation_time=t1.get_time group by  operationTime order by operationTime desc";
        List<PayServiceInfo> payServiceInfoList1 = sqlMapper.selectList(sql2, PayServiceInfo.class);
        String sql4 = "select t2.customer_name as customerName,t2.reservations_id as reservationsId,t2.medical_record_number as medicalRecordNumber," +
                "  (CASE t2.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,t2.customer_age as customerAge,t2.customer_phone as customerPhone ,t2.field_tc as fieldTc" +
                " from " + this.getTableName() + " t2 where id='" + id + "'";
        ConsultantInfo consultantInfo1 = sqlMapper.selectOne(sql4, ConsultantInfo.class);
        String getTime = "";
       /* Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        Double accountArrears = 0.00;
        for (PayServiceInfo info1 : payServiceInfoList1) {
            if (info1 != null) {
                if (info1.getPayServiceId() != null && info1.getPayServiceId() != "") {
                    if (info1.getPayServiceNum() != null) {
                        totleReceivable += (info1.getPayServicePriceTwo()) * (info1.getPayServiceNum());
                    }
                    if (info1.getFoldingPay() != null) {
                        totleFoldingPay += info1.getFoldingPay();
                    }
                    info1.setTotleReceivable(totleReceivable);
                    info1.setTotleFoldingPay(totleFoldingPay);
                }
            }
        }*/
        for (PayServiceInfo info : payServiceInfoList) {
            if (info != null) {
               /*if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPay += info.getServiceActualPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPay);
                    info.setAccountArrears(totleServiceActualPay - totleServiceActualPay);
                }*/
                if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                    info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                    info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                }
                getTime = info.getGetTime();
            }
        }
        model.addAttribute("revisitRecordList", revisitRecordList);
        model.addAttribute("payServiceInfoList", payServiceInfoList); //成交记录
        model.addAttribute("consultantInfo1", consultantInfo1);
        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("reservationsInfo", reservationsInfo);
        model.addAttribute("reservations", ShiroKit.getUser().getAccount());
        model.addAttribute("consultantInfoList", consultantInfoList);
        model.addAttribute("tcName", tcName);
        model.addAttribute("arrivalStore", customerInfo.getArrivalStore());
        model.addAttribute("id", id);
        model.addAttribute("firstVisitDate", firstVisitDate);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerId", id);
        return PREFIX + "consultantNoDetail.html";
    }

    /**
     * 回访姓名跳转页面 () 获取看诊详情  和  回访详情
     */
    @RequestMapping(value = "/consultantDetails/{cr}")
    public String consultantDetails(@PathVariable String cr, Model model) {
        String id = cr.split(",")[0];
        String reservationsId = cr.split(",")[1];
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }

        String selectsql = "select * from " + this.getTableName() + " where reservations_id='" + reservationsId + "' order by arrival_time asc limit 0,1";
        CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
        //看诊详情
        List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList(reservationsId);
        String tcName = "";
        String firstVisitDate = "";
        if (customerInfo != null) {
            tcName = customerInfo.getFieldTc();
            firstVisitDate = customerInfo.getFirstVisitDate();
        }
        for (ConsultantInfo info1 : consultantInfoList) {
            if (info1.getDiagnosisTime() != "" && info1.getDiagnosisTime() != null) {
                info1.setDiagnosisTimeOne(info1.getDiagnosisTime().substring(0, 10));
                info1.setDiagnosisTimeTwo(info1.getDiagnosisTime().substring(11, info1.getDiagnosisTime().length()));
            }
        }
        String sql = "select * from " + this.getTableName() + " t2 where reservations_id='" + reservationsId + "' order by t2.arrival_time desc limit 0,1";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql, ConsultantInfo.class);
        String sql1 = "select * from reservations_info where id='" + reservationsId + "' limit 0,1";
        //回访详情
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql1, ReservationsInfo.class);
        if (reservationsInfo != null) {
            model.addAttribute("huifang", reservationsInfo.getHuifang());
        } else {
            model.addAttribute("huifang", "");
        }
        List<AddVisitedInfo> revisitRecordList = service.getRevisitRecordList(reservationsId);

        //成交记录
        String tableName1 = tableHead + "deal_info";
        String sql3 = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime," +
                "sum(t1.service_actual_pay) as totleServiceActualPay,sum(t1.folding_pay) as totleFoldingPay," +
                "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "group_concat(distinct t2.pay_service_name) as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,t1.get_time as getTime ," +
                " group_concat(distinct t1.doctor) as doctor" +
                " from " + tableName1 + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.reservations_id='" + customerInfo.getReservationsId() + "' and pay_service_id is not null and t1.deal_status='已成交' " +
                " group by  operationTime order by operationTime desc";
        List<PayServiceInfo> payServiceInfoList = sqlMapper.selectList(sql3, PayServiceInfo.class);
        String sql2 = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,group_concat(distinct t1.remarks) as remarks," +
                " t1.folding_pay as foldingPay,t1.to_void_cause as toVoidCause,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,t1.get_time as getTime ,group_concat(distinct t1.doctor) as doctor\n" +
                " from " + tableName1 + " t1 left join pay_service_info t2 on t1.pay_service_id=t2.id " +
                " where t1.reservations_id='" + customerInfo.getReservationsId() + "' and pay_service_id is not null and t1.operation_time=t1.get_time group by  operationTime order by operationTime desc";
        List<PayServiceInfo> payServiceInfoList1 = sqlMapper.selectList(sql2, PayServiceInfo.class);

        String sql4 = "select t2.customer_name as customerName,t2.reservations_id as reservationsId,t2.medical_record_number as medicalRecordNumber," +
                "  (CASE t2.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,t2.customer_age as customerAge,t2.customer_phone as customerPhone ,t2.field_tc as fieldTc" +
                " from " + this.getTableName() + " t2 where id='" + id + "'";
        ConsultantInfo consultantInfo1 = sqlMapper.selectOne(sql4, ConsultantInfo.class);
       /* Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        Double accountArrears = 0.00;
        for (PayServiceInfo info1 : payServiceInfoList1) {
            if (info1 != null) {
                if (info1.getPayServiceId() != null && info1.getPayServiceId() != "") {
                    if (info1.getPayServiceNum() != null) {
                        totleReceivable += (info1.getPayServicePriceTwo()) * (info1.getPayServiceNum());
                    }
                    if (info1.getFoldingPay() != null) {
                        totleFoldingPay += info1.getFoldingPay();
                    }
                    info1.setTotleReceivable(totleReceivable);
                    info1.setTotleFoldingPay(totleFoldingPay);
                }
            }
        }*/
        for (PayServiceInfo info : payServiceInfoList) {
            if (info != null) {
                /*if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPay += info.getServiceActualPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPay);
                    info.setAccountArrears(totleServiceActualPay - totleServiceActualPay);
                }*/
                if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                    info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                    info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                }
            }
        }

        model.addAttribute("revisitRecordList", revisitRecordList);
        model.addAttribute("payServiceInfoList", payServiceInfoList);//成交记录
        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("consultantInfo1", consultantInfo1);
        model.addAttribute("reservationsInfo", reservationsInfo);
        model.addAttribute("reservations", ShiroKit.getUser().getAccount());
        model.addAttribute("consultantInfoList", consultantInfoList);
        model.addAttribute("tcName", tcName);
        model.addAttribute("arrivalStore", customerInfo.getArrivalStore());
        model.addAttribute("id", id);
        model.addAttribute("firstVisitDate", firstVisitDate);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("customerId", id);
        return PREFIX + "consultantDetails.html";
    }


    /**
     * 统计查询
     */
    @RequestMapping(value = "/total", method = RequestMethod.GET)
    @ResponseBody
    public List<ConsultantInfo> change(String param1, String param2, String param3, String param4, String param5, String param6, String param7) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        String contitionInfo = " AND t.customer_status != '作废' AND t.arrival_status!=1";
        if (StringUtils.isNotBlank(param1)) {
            contitionInfo += " and t.arrival_time >= '" + param1 + "' ";
        }
        if (StringUtils.isNotBlank(param2)) {
            contitionInfo += " and t.arrival_time <= '" + param2 + "' ";
        }
        if (StringUtils.isNotBlank(param4)) {
            contitionInfo += " and t.consumption_status = '" + param4 + "' ";
        }
        if (StringUtils.isNotBlank(param3)) {
            contitionInfo += " and t.follow_status = '" + param3 + "' ";
        }
        if (StringUtils.isNotBlank(param5)) {
            contitionInfo += " and t.field_tc = '" + param5 + "' ";
        }
        if (StringUtils.isNotBlank(param7)) {
            contitionInfo += " and t.deal_status = '" + param7 + "' ";
        }
        if (StringUtils.isNotBlank(param6)) {
            contitionInfo += " and (t.customer_name like CONCAT('%','" + param6 + "','%') or t.customer_phone like CONCAT('%','" + param6 + "','%') or " +
                    " t.wechat_number like CONCAT('%','" + param6 + "','%') or t.medical_record_number like CONCAT('%','" + param6 + "','%')) ";
        }
        String sql = "select count(*) as countNum from " + tableName + " t  where 1=1 " + contitionInfo +

                " union all select count(*) as countNum  from " + tableName + " t  where  t.xcustomer_status  in ('初到','初治','流失') " + contitionInfo +

                " union all select count(*) as countNum  from " + tableName + " t  where t.xcustomer_status='初到洁牙'" + contitionInfo +

                " union all select count(*) as countNum  from " + tableName + " t  where t.xcustomer_status='初治'" + contitionInfo +

                " union all select count(*) as countNum  from " + tableName + " t  where  t.xcustomer_status in ('复到','复到初治') " + contitionInfo +

                " union all select count(*) as countNum  from " + tableName + " t  where t.xcustomer_status='复到洁牙'" + contitionInfo +

                " union all select count(*) as countNum  from " + tableName + " t  where t.xcustomer_status='复到初治'" + contitionInfo +

                " union all select count(*) as countNum  from " + tableName + " t  where t.xcustomer_status='流失'" + contitionInfo +

                " union all select count(*) as countNum  from " + tableName + " t  where t.xcustomer_status='再消'" + contitionInfo;

        List<ConsultantInfo> tongJiInfoList = sqlMapper.selectList(sql, ConsultantInfo.class);
        return tongJiInfoList;
    }

    /**
     * 统计查询 预约回访 人数
     */
    @RequestMapping(value = "/totalNum", method = RequestMethod.GET)
    @ResponseBody
    public List<ConsultantInfo> changeNum(String param1, String param2, String param3, String param4, String param5, String param6) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        String contitionInfo = "";
        if (StringUtils.isNotBlank(param1)) {
            contitionInfo += " and t.appointment_time >= '" + param1 + "' ";
        }
        if (StringUtils.isNotBlank(param2)) {
            contitionInfo += " and t.appointment_time <= '" + param2 + "' ";
        }
        if (StringUtils.isNotBlank(param3)) {
            contitionInfo += " and t.follow_status = '" + param3 + "' ";
        }
        if (StringUtils.isNotBlank(param4)) {
            contitionInfo += " and t.consumption_status = '" + param4 + "' ";
        }
        if (StringUtils.isNotBlank(param5)) {
            contitionInfo += " and t.reservations = '" + param5 + "' ";
        }
        if (StringUtils.isNotBlank(param6)) {
            contitionInfo += " and t.deal_status = '" + param6 + "' ";
        }
        String sql = " select count(*) as countNum  from " + tableName + " t  where  t.follow_status='跟踪中'" + contitionInfo +

                " union all select count(*) as countNum  from " + tableName + " t  where t.follow_status='跟踪完成'" + contitionInfo +

                " union all select count(*) as countNum  from " + tableName + " t  where t.follow_status='跟踪结束'" + contitionInfo;

        List<ConsultantInfo> infoList = sqlMapper.selectList(sql, ConsultantInfo.class);
        return infoList;
    }

    @RequestMapping(value = "/consultantExport")
    public void consultantExport(@RequestParam(required = false) String startAppointmentTime,
                                 @RequestParam(required = false) String endAppointmentTime,
                                 @RequestParam(required = false) String reach,
                                 @RequestParam(required = false) String revisit,
                                 @RequestParam(required = false) String dealStatus,
                                 @RequestParam(required = false) String consumptionStatus,
                                 @RequestParam(required = false) String followStatus, HttpServletResponse response) {
        try {
            SearchCustomerInfo searchInfo = new SearchCustomerInfo();
            searchInfo.setReach(reach);
            searchInfo.setConsumptionStatus(consumptionStatus);
            searchInfo.setFollowStatus(followStatus);
            searchInfo.setStartAppointmentTime(startAppointmentTime);
            searchInfo.setEndAppointmentTime(endAppointmentTime);
            searchInfo.setReservations(revisit);
            searchInfo.setDealStatus(dealStatus);
            List<CustomerInfo> list = service.consultantExporttt(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 19; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("客户状态");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("病历号");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("姓名");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("性别");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("年龄");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("手机号");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("咨询项目");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("一级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("二级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("三级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("首次到店时间");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("到店时间");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("tc");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("成交状态");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("成交金额");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                CustomerInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                int customerAge = 0;
                if (map.getCustomerAge() != null) {
                    customerAge = map.getCustomerAge();
                }
                String medicalRecordNumber = map.getMedicalRecordNumber();
                String appointmentPayService = map.getAppointmentPayService();
                String arrivalTime = map.getArrivalTime();
                String customerStatus = "";
                if (map.getCustomerStatus() != null) {
                    customerStatus = map.getCustomerStatus();
                }
                String xcustomerStatus = "";
                if (map.getXcustomerStatus() != null) {
                    xcustomerStatus = map.getXcustomerStatus();
                }
                String customerPhone = "";
                if (map.getCustomerPhone() != null) {
                    customerPhone = map.getCustomerPhone();
                }
                String customerName = "";
                if (map.getCustomerName() != null) {
                    customerName = map.getCustomerName();
                }
                String sex = "";
                if (map.getSex() != null) {
                    sex = map.getSex();
                }
                String fieldTc = "";
                if (map.getFieldTc() != null) {
                    fieldTc = map.getFieldTc();
                }
                String oneChannel = map.getOneChannel();
                String twoChannel = map.getTwoChannel();
                String thrChannel = map.getThrChannel();
                String firstVisitDate = map.getFirstVisitDate();

                if (map.getDealStatus() != null) {
                    dealStatus = map.getDealStatus();
                }
                String actualAccountBalances = map.getActualAccountBalances();
                if (StringUtil.isEmpty(actualAccountBalances)) {
                    actualAccountBalances = "0.00";
                }
                String id = map.getId();
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 14; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                //row.getCell(0).setCellValue(customerStatus);
                row.getCell(0).setCellValue(xcustomerStatus);
                row.getCell(1).setCellValue(medicalRecordNumber);
                row.getCell(2).setCellValue(customerName);
                row.getCell(3).setCellValue(sex);
                row.getCell(4).setCellValue(customerAge);
                row.getCell(5).setCellValue(customerPhone);
                row.getCell(6).setCellValue(appointmentPayService);
                row.getCell(7).setCellValue(oneChannel);
                row.getCell(8).setCellValue(twoChannel);
                row.getCell(9).setCellValue(thrChannel);
                row.getCell(10).setCellValue(firstVisitDate);
                row.getCell(11).setCellValue(arrivalTime);
                row.getCell(12).setCellValue(fieldTc);
                row.getCell(13).setCellValue(dealStatus);
                row.getCell(14).setCellValue(actualAccountBalances);
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String getTableName() {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return tableName;
    }

}
